package com.situ.day18;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import org.junit.Test;


public class PreparedStatementDemo {
	@Test
	public void test1() {
		Connection conection = null;
		Statement statement = null;
		ResultSet resultSet = null;
		String name = "dfhgu' OR 1=1 -- y";
		String password = "34343";
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conection = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/java1812?useUnicode=true&characterEncoding=UTF-8", "root", "1234");
			String sql = "SELECT id,`name`,age,gender FROM student WHERE name='"+name+"' AND password='"+password+"';";
			statement = conection.createStatement();
			resultSet = statement.executeQuery(sql);
			while (resultSet.next()) {
				System.out.println("登录成功");
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	@Test
	public void testPreparedStatement() {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		//String name = "dfhgu' OR 1=1 -- y";
		//String password = "34343";
		String name = "lisi";
		String password = "123";
		try {
			connection = JDBCUtil.getConnection();
			String sql = "SELECT name,password FROM user WHERE name=? AND password=?;";
			//预编译sql
			preparedStatement = connection.prepareStatement(sql);
			//设置参数的值
			preparedStatement.setString(1, name);
			preparedStatement.setString(2, password);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				String userName = resultSet.getString("name");
				String userPassword = resultSet.getString("password");
				System.out.println(userName);
				System.out.println(userPassword);
				System.out.println("登录成功");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	@Test
	public void testPreparedStatementInsert() {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		String name = "张三";
		int age = 20;
		String gender = "男";
		try {
			connection = JDBCUtil.getConnection();
			String sql = "INSERT INTO student(name,age,gender) VALUES(?,?,?);";
			//预编译sql
			preparedStatement = connection.prepareStatement(sql);
			//设置参数的值
			preparedStatement.setString(1, name);
			preparedStatement.setInt(2, age);
			preparedStatement.setString(3, gender);
			int count = preparedStatement.executeUpdate();
			System.out.println(count);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	@Test
	public void testPreparedStatementInsert11() {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "INSERT INTO student(name,age,gender) VALUES(?,?,?);";
			//预编译sql
			preparedStatement = connection.prepareStatement(sql);
			for (int i = 1; i <= 10; i++) {
				//设置参数的值
				preparedStatement.setString(1, "张三" + i);
				preparedStatement.setInt(2, 20);
				preparedStatement.setString(3, "男");
				int count = preparedStatement.executeUpdate();
				System.out.println(count);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}
